#!/usr/bin/env python
# encoding: utf-8
import sys

sys.path.append('..')
import time
import pandas as pd
import numpy as np
from sklearn.impute import SimpleImputer
from sklearn.preprocessing import OneHotEncoder, StandardScaler
from sklearn.linear_model import Lasso
from sqlalchemy import create_engine
import pymysql
import warnings

from logs.mlog import log

warnings.filterwarnings("ignore")


def getMysqlDateFrame(sql, host, port, database, charset, user='dev',
                      password='vKITJVGT7dianJMXDNERlcK2zYEbVkutEShK69SFDxTlIJF3SjLlHCbhZcfw'):
    conn = pymysql.connect(host=host, port=port, user=user, passwd=password, db=database, charset='utf8')
    cur = conn.cursor()
    df = pd.read_sql_query(sql, conn)
    conn.close()
    return df


monitor_mysql_config = {
    "host": "10.37.50.86",
    "port": 4000,
    "user": "root",
    "password": "enntidb2021",
    "database": "dm_middle",
    "charset": "utf8"
}



def get_df():
    sql_getdf = """SELECT * FROM dm_middle.dm_middle_xz_passflow_pre_origin;"""
    df = getMysqlDateFrame(sql_getdf, **monitor_mysql_config)
    return df


def save_to_sql(joint_table, table_name):
    """
    :param joint_table: 传入的dataframe
    :param table_name: 表名
    """
    host = "10.37.50.86"
    user = "root"
    password = "enntidb2021"
    database = "dm_middle"

    con = create_engine("mysql+pymysql://{}:{}@{}:4000/{}?charset=utf8".format(user, password, host, database))
    conn = con.connect()
    # del_sql = f"""delete from cameratracecount where dateline = '{date}'"""
    # conn.execute(del_sql)
    # cur=con.cursor()
    joint_table.to_sql(table_name, con, index=False, if_exists='append')
    # pd.io.sql.to_sql(joint_table,table_name,con,index=False,if_exists='append')
    conn.close()
    log.info("上传成功...")


def get_num(x):
    if x['tourist_holiday_tq_1'] == 0:
        return x['tourist_num_tq_1']
    else:
        return x['tourist_holiday_tq_1']


def data_process(df):
    df.iloc[:, 12].fillna(value='非节假日', inplace=True)
    df.iloc[:, 13].fillna(value=0, inplace=True)
    df.iloc[:, 18].fillna(value='非活动日', inplace=True)
    df.loc[:, 'max_temperature'] = df.loc[:, 'max_temperature'].fillna(method='bfill', axis=0)
    df.loc[:, 'min_temperature'] = df.loc[:, 'min_temperature'].fillna(method='bfill', axis=0)
    windp = df.loc[:, 'windp'].values.reshape(-1, 1)
    imp_mode = SimpleImputer(strategy="median")
    df.loc[:, 'windp'] = imp_mode.fit_transform(windp)
    humidity = df.loc[:, 'humidity'].values.reshape(-1, 1)
    imp_mode = SimpleImputer(strategy="median")
    df.loc[:, 'humidity'] = imp_mode.fit_transform(humidity)
    df.loc[df['weather'].isnull(), 'weather'] = np.nan
    weather = df.loc[:, 'weather'].values.reshape(-1, 1)
    imp_mode = SimpleImputer(strategy="most_frequent")
    df.loc[:, 'weather'] = imp_mode.fit_transform(weather)
    df.loc[df['baidu_num'] == 0, 'baidu_num'] = np.nan
    df.loc[:, 'baidu_num'] = df.loc[:, 'baidu_num'].fillna(method='bfill', axis=0)
    df['tourist_num_tq'] = df.apply(get_num, axis=1)
    data1 = df.drop(df.columns[[20, 21, 22, 23]], axis=1)
    data1['weather_'] = data1['weather'].apply(lambda x: 0 if '雪' in str(x) else 1)
    # data1=data1.dropna()
    data1 = data1.sort_values('enter_date', ascending=False).reset_index().drop(columns='index', axis=1)
    # print(data1.head())
    data1['holiday_rank'] = data1['holiday_rank'].astype(str)
    data1['weather_'] = data1['weather_'].astype(str)
    data1['covid_class'] = data1['covid_class'].astype(str)
    data1['activity_class'] = data1['activity_class'].astype(str)
    return data1


def now_datetime():
    '''
    获取当前时间
    :return: datetime
    '''
    return time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))


def tourist_num_predict(data1, cols, cols_):
    model_ohe = OneHotEncoder(sparse=False)  # 建立OneHotEncode对象
    ohe_matrix = model_ohe.fit_transform(data1[cols])  # 直接转换
    sacle_matrix = data1.loc[:, cols_]  # 获得要转换的矩阵
    model_scaler = StandardScaler()
    data_scaled = model_scaler.fit_transform(sacle_matrix)  # 标准化处理
    X = np.hstack((data_scaled, ohe_matrix))
    Xtrain, Xtest, Ytrain, Ytest = X[1:], X[0], data1['tourist_num'][1:], data1['tourist_num'][0]
    reg = Lasso(alpha=0.8, fit_intercept=True, normalize=True, \
                precompute=False, copy_X=True, max_iter=1000, tol=1e-5, \
                warm_start=False, positive=False, random_state=None, \
                selection='cyclic')
    reg.fit(Xtrain, Ytrain)
    y_pred = reg.predict(Xtest.reshape(1, 25))
    tourist_num = int(y_pred.astype(int)[0])
    scenic_name = data1.iloc[0, 1]
    enter_date = data1.iloc[0, 3]
    df_predict = pd.DataFrame(columns=['scenic_name', 'enter_date', 'tourist_num', 'update_time', 'project_name'])
    update_time = now_datetime()
    df_predict.loc[0, ['scenic_name', 'enter_date', 'tourist_num', 'update_time', 'project_name']] = [scenic_name,
                                                                                                      enter_date,
                                                                                                      tourist_num,
                                                                                                      update_time, "西藏"]
    return df_predict



if __name__ == "__main__":
    log.info('start...')
    df = get_df()
    data1 = data_process(df)
    cols = ["weather_", "holiday_class", "holiday_rank", "season_name", "covid_class", "activity_class"]
    cols_ = ['max_temperature', 'min_temperature', 'windp', 'baidu_num', 'holiday_day', 'tourist_num_tq',
             'tourist_num_tq_3', 'tourist_num_tq_4']
    df_predict = tourist_num_predict(data1, cols, cols_)
    save_to_sql(joint_table=df_predict, table_name='dm_middle_xz_passflow_pre_res')
    log.info('succ...')
